<?php

// +----------------------------------------------------------------------

// | Description: 客户

// +----------------------------------------------------------------------

// | Author: zl

// +----------------------------------------------------------------------



namespace app\crm\controller;



use app\admin\controller\ApiCommon;

use app\crm\model\BalanceLog;

use app\crm\model\Setting;

use app\crm\model\SettingCategory;

use app\crm\model\Service;

use app\crm\model\ReturnVist;

use app\crm\model\ReturnVistT;

use app\crm\model\SendMsgModel;

use app\crm\model\Customer as CustomerModel;

use app\crm\model\TransferShop;

use app\crm\model\CustomerConfig as CustomerConfigModel;

use app\crm\model\CustomerGrade as CustomerGradeModel;

use app\crm\model\ReserveandLost;

use think\response\Json;

use think\facade\Db;

use think\facade\Request;

use app\admin\model\Coupon as CouponModel;

use app\common\traits\ActionRecordTrait;

use app\shop\model\unionpay\AppConfig;
use app\shop\model\unionpay\AppUtil;
use app\shop\model\unionpay\Test;
use app\crm\model\MiniProgramModel;
use app\shop\model\RefundPayModel;


class Ztool extends ApiCommon
{
    protected $customerModel;

    protected $customerConfigModel;

    /**

     * 用于判断权限

     * @permission 无限制

     * @allow 登录用户可访问

     * @other 其他根据系统设置

     **/

    public function initialize()

    {

        $action = [

            'permission' => ['check_service_log','wx_send','daochu_wenjuan','shophufpt','update_paiban','shop_customer_type','renjun','mini_cj_customer','add_account','case_updata','order_goods','daochugoods','daochu_case','delete_k3','zidingyi1','rollback_case'],

            'allow' => ['users', 'info']

        ];

        event('CheckAuth',$action);

        $request = $this->request;

        $a = strtolower($request->action());



        $this->customerModel = new CustomerModel();

        $this->customerConfigModel = new CustomerConfigModel();

        if (!in_array($a, $action['permission'])) {

            parent::initialize();

        }

    }

    public function check_service_log(){
        echo "检测核销记录";

        $shop_id = [73782775132896]; // zhenghongcheng

        $staff_ids = [498];
        
        $start_time = strtotime('2023-06-01');

        $end_time = strtotime('2023-06-30 23:59:59');

        $list1 = Db::name('service_log')->where([['shop_id', 'in', $shop_id], ['nurse_id_one', 'in', $staff_ids], ['nurse_id_two', '=', 0], ['create_time', 'between', "$start_time,$end_time"]])->group('nurse_id_one')->column('count(*) as sum','nurse_id_one');

        echo "<pre>";
        print_r($list1);
        echo "</pre>";
        // exit;

        $list2 = Db::name('service_log')->where([['shop_id', 'in', $shop_id], ['nurse_id_one', 'in', $staff_ids], ['nurse_id_two', '>', 0], ['create_time', 'between', "$start_time,$end_time"]])->group('nurse_id_one')->column('count(*)*0.5 as sum','nurse_id_one');

        echo "<pre>";
        print_r($list2);
        echo "</pre>";

        $list3 = Db::name('service_log')->where([['shop_id', 'in', $shop_id], ['nurse_id_one', '>', 0], ['nurse_id_two', 'in', $staff_ids], ['create_time', 'between', "$start_time,$end_time"]])->group('nurse_id_two')->column('count(*)*0.5 as sum','nurse_id_two');

        echo "<pre>";
        print_r($list3);
        echo "</pre>";

    }

    // public function (){

    //     $where = [];

    //     $shop_ids = [];

    //     $start_time = strtotime('2023-01-01 00:00:00');

    //     $end_time = strtotime('2023-12-31 23:59:59');

    //     array_push($where,[$where,['shop_id','in',$shop_ids]]);

    //     array_push($where,[$where,['status','=',1]]);

    // }

    public function wx_send()
    {

        // $gzh_openids = Db::name('mini_program_fans')->alias('m')

        // ->join('wx_gzh_fans g', 'g.unionId = m.unionId', 'LEFT')

        // ->where([['g.openid','>','0']])

        // ->column('g.openid');

        // echo "<pre>";
        // print_r($gzh_openids);
        // echo "</pre>";

        // $model = new SendMsgModel();

        // $gzh_openids = ['orXbIs5Il9x5cC9wT_kP-GzS29Wk','orXbIs9RDp16tEveSqcWIYB82R6c'];

        // echo "<pre>";
        // print_r($gzh_openids);
        // echo "</pre>";
        // // exit;

        // $num = 0;
        // foreach ($gzh_openids as $key => $value) {
        //     $model->wx_send_cs($value,0);
        //     $num++;
        // }

        // echo "发送".count($gzh_openids).'-成功'.$num;

        
        echo '1122244';
        // 直管店客户批量转店
        // $customer_ids = Db::name('customer')->where('shop_id','78852616335812')->column('id');
        $where = [];
        array_push($where,['shop_id','=','79154893551569']);
        // array_push($where,['king_no','like','30131%']);
        $customer_ids = Db::name('customer')->where($where)->column('id');

        // echo "<pre>";
        // print_r($customer_ids);
        // echo "</pre>";
        // exit;

        $start_time = strtotime('2024-06-01 00:00:00');
        $end_time = strtotime('2024-06-04 23:59:59');
        $tag_shop_id = '73782774822370';
        foreach ($customer_ids as $key => $value) {
            // 修改客户归属门店
            Db::name('customer')->where('id','=',$value)->update(['shop_id'=>$tag_shop_id]);

            // 修改客户预存门店归属
            Db::name('customer_balance')->where('customer_id','=',$value)->update(['shop_id'=>$tag_shop_id]);

            // 修改客户卡项
            Db::name('service')->where('customer_id','=',$value)->update(['shop_id'=>$tag_shop_id]);

            // 修改客户核销记录
            //Db::name('service_log')->where([['customer_id','=',$value],['create_time','between',"$start_time,$end_time"]])->update(['shop_id'=>$tag_shop_id]);
        }

        echo 'end';
    }

    public function daochu_wenjuan(){
        // echo 'daochu_wenjuan';

        $param = [
            'is_export' => 1,
            'create_time_start' => '',
            'create_time_end' => '',
            'feed_type' => '',
            'limit' => 10,
            'page' => 1,
            'shop_id' => '',
            'total' => 0,
            'wenjuanID' => 6,
            'wenjuan_id' => 6,
        ];

        $model = new MiniProgramModel();

        $data = $model->getManyiduList($param);

        // echo "<pre>";
        // print_r($data);
        // echo "</pre>";

        $title = '满意度明细导出';

        $downUrl = exportExcelData($title,$data['callName'],$data['list'],'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>"; 
    }

    public function update_paiban(){
        echo "update_paiban";

        // $delete_where = [];

        // array_push($delete_where,['time','=','20240103']);

        // Db::name('customer_balance_tj')->where($delete_where)->delete();
        // $where = [];

        // array_push($where,['time','like','202301%']);
        // // array_push($where,['shop_person_id','=','13']);

        // $errorList = Db::name('shop_person_schedule')->where($where)->select()->toArray();

        // echo "<pre>";
        // print_r($errorList);
        // echo "</pre>";

        // foreach ($errorList as $key => $value) {
        //     $year = substr($value['time'],0,4);
        //     $mon = substr($value['time'],4,2);
        //     $day = substr($value['time'],6,2);

        //     $newYear = $year+1;

        //     echo $rightDate = $newYear.$mon.$day;
        //     echo "<br/>";

        //     Db::name('shop_person_schedule')->where('id','=',$value['id'])->update(['time'=>$rightDate]);

            
        // }

    }

    public function shop_customer_type()
    {

        // $title = ['71'=>'C','72'=>'B','73'=>'A'];

        $where = [];

        array_push($where,['type','=','1']);
        array_push($where,['shop_name','not in','营销中心']);
        array_push($where,['status','=','1']);
        array_push($where,['market_id','in',[4,6]]);

        $shop_list = DB::name('shop')->where($where)->column('shop_id');

        // 门店
        $shop_names = Db::name('shop')->where([['shop_id', 'in', $shop_list]])->column('shop_name','shop_id');

        // $type = '71';

        $start_time = strtotime('2023-01-01');
        $end_time = strtotime(2024-01-01);

        $list = [];
        foreach ($shop_list as $key => $value) {
            $where = [];

            array_push($where,['member_classification','in',[71,72,73]]);
            array_push($where,['shop_id','=',$value]);
            array_push($where,['final_consumption','between',"$start_time,$end_time"]);

            $res = Db::name('customer')->where($where)->column('id');

            // echo "<pre>";
            // print_r($res);
            // echo "</pre>";
            // exit;

            $a = 0;
            $b = 0;
            $c = 0;
            foreach ($res as $k => $v) {
                
                $where = [];

                array_push($where,['uid','=',$v]);
                array_push($where,['type','=','3']); // 收款单
                array_push($where,['refund_type','=','101.02']);
                array_push($where,['status','=','2']);
                array_push($where,['deal_time','between',"$start_time,$end_time"]);

                $money1 = Db::name('shop_refund_order')->where($where)->value('SUM(pay_amount)');

                $money1 = $money1 ?? '0.00';

                $where = [];

                array_push($where,['uid','=',$v]);
                array_push($where,['type','=','1']); // 退款单
                array_push($where,['refund_type','=','101.02']);
                array_push($where,['status','=','2']);
                array_push($where,['deal_time','between',"$start_time,$end_time"]);

                $money2 = Db::name('shop_refund_order')->where($where)->value('SUM(pay_amount)');

                $money2 = $money2 ?? '0.00';

                $consumption = bcsub($money1,$money2,2);

                switch ($consumption)
                {
                    case $consumption >= 50000: // A类客户
                        $a++;
                        break;
                    case $consumption >= 15000: // B类客户
                    // case $consumption < 50000:
                        $b++;
                        break;
                    case $consumption >= 5000: // C类客户
                    // case $consumption < 15000:
                        $c++;
                        break;
                    default:
                        break;
                }
            }

            $one = [];

            $one['shop_name'] = $shop_names[$value];
            $one['a'] = $a;
            $one['b'] = $b;
            $one['c'] = $c;

            array_push($list,$one);
        }
        

        // $where = [];

        // array_push($where,['member_classification','=',72]);
        // array_push($where,['shop_id','in',$shop_list]);
        // // array_push($where,['create_time','between',"$start_time,$end_time"]);

        // $res2 = Db::name('customer')->where($where)->group('shop_id')->column('count(*)','shop_id');

        // $where = [];

        // array_push($where,['member_classification','=',73]);
        // array_push($where,['shop_id','in',$shop_list]);
        // // array_push($where,['create_time','between',"$start_time,$end_time"]);

        // $res3 = Db::name('customer')->where($where)->group('shop_id')->column('count(*)','shop_id');

        

        // $list = [];

        // foreach ($shop_list as $key => $value) {
        //     $one = [];

        //     $one['shop_name'] = $shop_names[$value];
        //     $one['customer_type1'] = $res1[$value] ?? '';
        //     $one['customer_type2'] = $res2[$value] ?? '';
        //     $one['customer_type3'] = $res3[$value] ?? '';

        //     array_push($list,$one);

        // }

        echo "<pre>";
        print_r($list);
        echo "</pre>";
// exit;
        $callName = [

            'shop_name' =>'门店',

            'a' =>'a类顾客',
            'b' =>'B类顾客',
            'c' =>'C类顾客',
            
        ];

        $downUrl = exportExcelData('顾客分类统计',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";

    }

    public function renjun()
    {
        echo "renjun";
		
        $date = date('Y-m-d');

        $where = [];

        array_push($where,['type','in',[1,3]]);
        array_push($where,['shop_name','not in','营销中心']);
        array_push($where,['status','=','1']);
		//array_push($where,['shop_id','=','73782774707242']);

        $shop_list = DB::name('shop')->where($where)->column('shop_id');

        // echo "<pre>";
        // print_r($shop_list);
        // echo "</pre>";
        // exit;
		
		$start_time =strtotime($_GET['start_time']);
		$end_time=strtotime($_GET['end_time'])+86399;
		
		// $start_time = strtotime(date('Y-m-01'));
		// $end_time = strtotime(date('Y-m-t'))+86399;

        // echo "开始时间：".$start_time;echo "<br/>";
        // echo "结束时间：".$end_time;

        // exit;
		
        // 门店
        $shop_names = Db::name('shop')->where([['shop_id', 'in', $shop_list]])->column('shop_name','shop_id');

        $model = new ReserveandLost();
		
		
		
        $list = [];
        foreach ($shop_list as $key => $value) {

            // $customer_ids = $model->cj_customer([$value],[]);

            // $ids = $model->baoyou_customer($customer_ids,$date);

            $where = [];

            array_push($where,['shop_id','=',$value]);
            array_push($where,['type','=','3']); // 收款单
            array_push($where,['refund_type','=','101.02']);
            array_push($where,['status','=','2']);
			array_push($where,['deal_time','between',"$start_time,$end_time"]);
            array_push($where,['business_type','=','4']); // distinct

            $num4 = Db::name('shop_refund_order')->where($where)->count();
			
			// $num4 = Db::name('shop_refund_order')->group('uid')->where($where)->column('sum(pay_amount) as amount,shop_id,uid');
			
			// $numT =0; 
			// foreach ($num4 as $k => $v) {
				
			// 	if($v['amount']>=2980){
			// 		$numT++;
			// 	}
				
			// }
			
			// echo "<pre>";
			// print_r($num4);
			// echo "</pre>";
            // $money1 = $money1 ?? '0.00';

            // $where = [];

            // array_push($where,['uid','in',$ids]);
            // array_push($where,['type','=','1']); // 退款单
            // array_push($where,['refund_type','=','101.02']);
            // array_push($where,['status','=','2']);

            // $money2 = Db::name('shop_refund_order')->where($where)->value('SUM(pay_amount)');

            // $money2 = $money2 ?? '0.00';

            // $amount = bcsub($money1,$money2,2);

            $one =[];
            $one['shop_name'] = $shop_names[$value];
            $one['num'] = $num4;
            
            array_push($list,$one);
			

        }
		//exit;
        // echo "<pre>";
        // print_r($list);
        // echo "</pre>";exit;

        $callName = [

            'shop_name' =>'门店',

            'num' => '数量',
            
        ];

        $downUrl = exportExcelData('销售数量',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";
        
    }
	
	
	public function shophufpT()
	{
	    echo "renjun";
		
	    $date = date('Y-m-d');
	
	    $where = [];
	
	    array_push($where,['type','in',[1,3]]);
	    array_push($where,['shop_name','not in','营销中心']);
	    array_push($where,['status','=','1']);
		//array_push($where,['shop_id','=','73782774707242']);
	
	    $shop_list = DB::name('shop')->where($where)->column('shop_id');
	
	    // echo "<pre>";
	    // print_r($shop_list);
	    // echo "</pre>";
	    // exit;
		
		
		$start_time = 1709222400;
		$end_time = 1711900799;
		
	    // 门店
	    $shop_names = Db::name('shop')->where([['shop_id', 'in', $shop_list]])->column('shop_name','shop_id');
	
	    $model = new ReserveandLost();
		
		
		
	    $list = [];
	    foreach ($shop_list as $key => $value) {
	
	        // $customer_ids = $model->cj_customer([$value],[]);
	
	        // $ids = $model->baoyou_customer($customer_ids,$date);
	
	        $where = [];
	
	        array_push($where,['shop_id','=',$value]);
	        array_push($where,['type','=','3']); // 收款单
	        array_push($where,['refund_type','=','101.02']);
	        array_push($where,['status','=','2']);
			array_push($where,['deal_time','between',"$start_time,$end_time"]);
	        //array_push($where,['business_type','=','4']);  distinct
	
	        //$num4 = Db::name('shop_refund_order')->where($where)->count();
			
			$num4 = Db::name('shop_refund_order')->group('uid')->where($where)->column('sum(pay_amount) as amount,shop_id,uid');
			
			$numT =0; 
			foreach ($num4 as $k => $v) {
				
				if($v['amount']>=2980){
					$numT++;
				}
				
			}
			
			
	        $one =[];
	        $one['shop_name'] = $shop_names[$value];
	        $one['num'] = $numT;
	        
	        array_push($list,$one);
			
	
	    }
		//exit;
	    // echo "<pre>";
	    // print_r($list);
	    // echo "</pre>";exit;
	
	    $callName = [
	
	        'shop_name' =>'门店',
	
	        'num' => '数量',
	        
	    ];
	
	    $downUrl = exportExcelData('累计消费金额是2980',$callName,$list,'xlsx',1);
	
	    echo "<script language='javascript'>";
	
	    echo " location='".$downUrl."';";
	
	    echo "</script>";
	    
	}
	
	
	
    public function mini_cj_customer(){
        // echo 'mini_cj_customer';

        $customer_ids = Db::name('mini_program_fans')->alias('m')->join('customer c','c.id = m.customerId','LEFT')->where([['c.cj_money','>=','500'],['m.create_time','>','2022-12-31 23:59:59']])->column('c.id');

        // $customer_ids = Db::name('customer')->alias('c')->where([['c.cj_money','>=','500'],['c.cj_time','>=','1672502400']])->count();

        // echo $customer_ids;

        echo "<pre>";
        print_r($customer_ids);
        echo "</pre>";


        // $gzh_openids = Db::name('mini_program_fans')->alias('m')

        // ->join('wx_gzh_fans g', 'g.unionId = m.unionId', 'LEFT')

        // ->where([['g.openid','>','0']])

        // ->column('g.openid');
    }

    public function add_account(){
        // echo "新建账户";

        // $where = [];

        // array_push($where,['type','<>',2]);
        // array_push($where,['status','=',1]);
        // // array_push($where,['shop_type','=',1]); // 私户

        // $list = Db::name('shop')->where($where)->column('id,shop_name,shop_id,province_name,city_name,country_name,address,shop_gonghu_cusid,shop_gonghu_appid,shop_gonghu_privateKey');

        // echo "<pre>";
        // print_r($list);
        // echo "</pre>";

        // $callName = [

        //     'id' =>'ID',

        //     'address' => '地址',
            
        // ];

        // $downUrl = exportExcelData('门店信息',$callName,$list,'xlsx',1);

        // echo "<script language='javascript'>";

        // echo " location='".$downUrl."';";

        // echo "</script>";


        // exit;

        if (empty($_FILES['file'])) {

            echo '请选择上传文件<br><br><a href="http://sunburn.178590.com/return/add_account.html">返回【会员基本信息导入】页面</a>';exit;

        }

        $tmp_name = $_FILES['file']['tmp_name'];

        $importData = readExcelData($tmp_name);

        $RefundPay = new RefundPayModel();

        $length = 8;
        // $addData = [];
        foreach ($importData as $key => $value) {
            
            
            // exit;

            $termno = str_pad($value[0], $length, "0", STR_PAD_LEFT);
            $appid = str_pad($value[8], $length, "0", STR_PAD_LEFT);

            $addtermRequest = [

                'cusid' => $value[7],

                'appid' => $appid,

                'private_key' => $value[9],

                'termno' => $termno,

                'termaddress' => $value[3].'-'.$value[4].'-'.$value[5].'-'.$value[6],

            ];

            // echo "<pre>";
            // print_r($addtermRequest);
            // echo "</pre>";

            $res = $RefundPay->addterm($addtermRequest);

            if($res['retcode'] == 'SUCCESS'){

                echo $value[1]."<br/>";
                echo "<pre>";
                print_r($res);
                echo "</pre>";

                echo "<br/>";
                echo "<br/>";

                Db::name('shop')->where('shop_id',$value[2])->update(['termno_g' => $termno]);

            }else{

                echo $value[1]."<br/>";
                echo "<pre>";
                print_r($res);
                echo "</pre>";

                echo "<br/>";
                echo "<br/>";

            }

        }

        echo 'over';

    }

    public function case_updata()
    {
        $where = [];

        $tag_time = strtotime(date('Y-m',time()));

        echo $tag_time;

        array_push($where,['sp_excellent_time','<',$tag_time]);
        array_push($where,['selection_results','=','1']);

        $case_list = Db::name('case')->where($where)->select()->toArray();

        echo "<pre>";
        print_r($case_list);
        echo "</pre>";

        // foreach ($case_list as $key => $value) {
            
        //     Db::name('case')->where('id','=',$value['id'])->update(['selection_results' => 4]);

        // }
    }

    public function order_goods(){
        echo "order_goods";

        $start_time = strtotime("2024-07-01 00:00:00");
        $end_time = strtotime("2024-07-31 23:59:59");

        $where = [];

        array_push($where,['so.order_amount','=','0.00']);
        array_push($where,['so.status','=',2]);
        array_push($where,['so.is_pay','=',1]);
        array_push($where,['so.deal_time','between',"$start_time,$end_time"]);

        $res = Db::name('shop_order_goods')->alias('sog')

        ->join('shop_order so', 'so.id = sog.order_id', 'LEFT')

        ->where($where)

        ->limit(16000,5000)

        ->column('so.create_time,so.uid,so.shop_id,so.order_no,so.business_type,so.order_type,so.operator_name,so.is_experience,so.is_outreach,sog.goods_name,sog.is_project,sog.category_id,sog.goods_id,sog.goods_price,sog.pay_price,sog.number,sog.service_number,sog.end_time,so.description');
		
		//echo count($res);exit;
		
        // echo "<pre>";
        // print_r($res);
        // echo "</pre>";
        // exit;

        $customer_ids = array_values(array_unique(array_column($res,'uid')));

        $customer_info = Db::name('customer')->where('id','in',$customer_ids)->column('customer_name,telephone,sex','id');

        $shop_ids = array_values(array_unique(array_column($res,'shop_id')));

        $shop_info = Db::name('shop')->where('shop_id','in',$shop_ids)->column('shop_name,market_name','shop_id');

        // echo "<pre>";
        // print_r($shop_ids);
        // echo "</pre>";
        // exit;

        $business_type = ['1' => '疤痕','2' => '项目','3' => '青春痘','4' => '护肤品'];

        $order_type = ['1' => '新单','2' => '补单'];

        $list = [];

        foreach ($res as $key => $value) {
            
            $one = [];

            $one['create_time'] = date('Y-m-d',$value['create_time']) ?? '';
            $one['customer_name'] = $customer_info[$value['uid']]['customer_name'] ?? '';
            $one['telephone'] = $customer_info[$value['uid']]['telephone'] ?? '';
            $one['sex'] = $customer_info[$value['uid']]['sex'] = 1 ? '男' : '女';
            $one['shop_name'] = $shop_info[$value['shop_id']]['shop_name'] ?? '';
            $one['market_name'] = $shop_info[$value['shop_id']]['market_name'] ?? '';
            $one['order_no'] = $value['order_no'] ?? '';
            $one['business_type'] = $business_type[$value['business_type']] ?? '';
            $one['order_type'] = $order_type[$value['order_type']] ?? '';
            $one['operator_name'] = $value['operator_name'] ?? '';
            $one['is_experience'] = $value['is_experience'] == 1 ? '是' : '否';
            $one['is_outreach'] = $value['is_outreach'] == 1 ? '是' : '否';
            $one['goods_name'] = $value['goods_name'] ?? '';
            $one['is_project'] = $value['is_project'] == 1 ? '服务' : '商品';
            $one['goods_price'] = $value['goods_price'] ?? '';
            $one['pay_price'] = $value['pay_price'] ?? '';
            $one['number'] = $value['number'] ?? '';
			$one['description'] = $value['description'] ?? '';
            $one['service_number'] = $value['service_number'] ?? '';
            $one['end_time'] = $value['end_time'] > 0 ? date('Y-m-d',$value['end_time']) : '';

            array_push($list,$one);
        }

        // echo "<pre>";
        // print_r($list);
        // echo "</pre>";

        $callName = [

            'create_time' => '录入时间',
            'customer_name' => '客户姓名',
            'telephone' => '电话',
            'sex' => '性别',
            'shop_name' => '门店',
            'market_name' => '市场',
            'order_no' => '订单编号',
            'business_type' => '业务类型',
            'order_type' => '订单类型',
            'operator_name' => '录单员',
            'is_experience' => '是否体验',
            'is_outreach' => '是否外联',
            'goods_name' => '商品名称',
            'is_project' => '商品类型',
            'number' => '数量',
            'goods_price' => '标准单价',
            'pay_price' => '销售单价',
            '' => '销售金额',
            'service_number' => '开卡次数',
            'end_time' => '到期时间',
			'description' => '备注',

        ];

        $downUrl = exportExcelData('0元单导出记录明细',$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";
    }

    public function daochugoods(){
        echo "daochugoods";

        // $start_time = strtotime("2024-03-01 00:00:00");
        // $end_time = strtotime("2024-03-31 23:59:59");

        $goodName = "眼部5D精雕";

        $where = [];

        array_push($where,['goods_name','=',$goodName]);

        $order_ids = Db::name('shop_order_goods')->where($where)->column('order_id');

        $where = [];

        array_push($where,['id','in',$order_ids]);
        array_push($where,['status','=',2]);
        array_push($where,['is_pay','=',1]);

        $customer_ids = Db::name('shop_order')->where($where)->column('uid');

        $list = Db::name('customer')->where('id','in',$customer_ids)->column('customer_name,telephone');

        echo "<pre>";
        print_r($list);
        echo "</pre>";
        // exit;

        

        $callName = [

            
            'customer_name' => '客户姓名',
            'telephone' => '电话',

        ];

        $downUrl = exportExcelData($goodName,$callName,$list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";
    }

    public function daochu_case()
    {
        echo "案例";

        $st = strtotime('2024-04-01 00:00:00');
        $et = strtotime('2024-05-31 00:00:00');

        $where = [];

        array_push($where,['age','between','0,18']);

        $customers = Db::name('customer')->where($where)->column('id');

        echo "<pre>";
        print_r($customers);
        echo "</pre>";
        // exit;

        $where = [];

        // array_push($where,['selection_results','in',[2,3]]);
        array_push($where,['customer_id','in',$customers]);

        $list = Db::name('case')->where($where)->count();

        echo "<pre>";
        print_r($list);
        echo "</pre>";
        exit;

        $customer_list = Db::name('customer')->where('id','in',$list)->column('customer_name,telephone');
        // echo "<pre>";
        // print_r($customer_list);
        // echo "</pre>";

        $callName = [

            
            'customer_name' => '客户姓名',
            'telephone' => '电话',

        ];

        $downUrl = exportExcelData('申请案例',$callName,$customer_list,'xlsx',1);

        echo "<script language='javascript'>";

        echo " location='".$downUrl."';";

        echo "</script>";
    }

    public function delete_k3(){
        $k3Code = '40303.01';

        $where = [];
        array_push($where,['king_no','like',$k3Code."%"]);

        $customer_ids = Db::name('customer')->where($where)->column('id');

        echo "<pre>";
        print_r($customer_ids);
        echo "</pre>";
        exit;

        foreach ($customer_ids as $key => $value) {
            // 清除客户K3代码
            Db::name('customer')->where('id','=',$value)->update(['king_no'=>"",'fcustId'=>""]);

        }
    }

    public function zidingyi1(){
        $where = [];

        $balance_category_id = [3,4];
        array_push($where,['balance_category_id','in',$balance_category_id]);

        $customer_ids = Db::name('customer_balance')->where($where)->group('customer_id')->column('count(*) as num, customer_id');

        $list = [];
        foreach ($customer_ids as $key => $value) {
            if($value['num'] > 2){
                // echo $value['num'];
                // echo "<br/>";
                array_push($list,$value['customer_id']);
            }
        }
        echo "<pre>";
        print_r($list);
        echo "</pre>";
        // exit;
        
        foreach ($list as $key => $value) {
            $where = [];

            array_push($where,['customer_id','=',$value]);
            array_push($where,['balance_category_id','in',$balance_category_id]);
            $ids = Db::name('customer_balance')->where($where)->order('id DESC')->limit(0,2)->column('id');

            $delete_where = [];

            array_push($delete_where,['id','in',$ids]);

            Db::name('customer_balance')->where($delete_where)->delete();

            // echo "<pre>";
            // print_r($ids);
            // echo "</pre>";
            // exit;
        }

        
        
    }

    public function rollback_case()
    {
        $last_mon = date('m',strtotime('-1 month'));

        $last_year =  date('Y',strtotime('-1 month'));

        $t = date('t',strtotime('-1 month'));

        $start_time = strtotime($last_year.'-'.$last_mon.'-01 00:00:00');
        $end_time = strtotime($last_year.'-'.$last_mon.'-'.$t.' 23:59:59');

        $where = [];

        array_push($where,['sp_excellent_time','between',"$start_time,$end_time"]);
        array_push($where,['selection_results','=','1']);

        $list = Db::name('case')->where($where)->select()->toArray();

        echo "<pre>";
        print_r($list);
        echo "</pre>";
        exit;

        // echo $start_time;
        // echo "<br/>";
        // echo $end_time;

    }

}